	SELECT	C.DIRECTION, C.START_DT, 
	CASE UPPER(C.DWELLING_TYPE_U)  WHEN 'SFU' THEN 'SFU' ELSE 'MDU' END DWELLING,
	COALESCE(PRODUCT_NM, 'ALL') PRODUCT, 
	COALESCE(CASE C.NATIONAL_ID WHEN 'YES' THEN 'NATIONAL'  WHEN 'NO' THEN 'REGIONAL' END, 'BOTH') LOCATION , 
	COALESCE(C.REGION, 'ALL') REGION,
	YR,
	MN,
	TRIM(MARKET) MARKET,
	TRIM(STATE_CD) STATE,
	CASE WHEN INDEX(AREA_MGR,'(') >0 THEN SUBSTR(AREA_MGR, 1, INDEX(AREA_MGR,'(') -2) ELSE TRIM(AREA_MGR) END AREA_MGR,
	CASE WHEN INDEX(ACCT_MGR,'(') >0 THEN SUBSTR(ACCT_MGR, 1, INDEX(ACCT_MGR,'(') -2) ELSE TRIM(ACCT_MGR)  END ACCT_MGR,
	TRIM(PORTFOLIO) PORTFOLIO,
	TRIM(TRAC_ID) TRAC_ID,
	TRIM(PROPERTY_NAME_U)  PROPERTY_NAME_U,
	QTY		
	FROM			
	( ---C
		SELECT
		 REGION_DESC_U REGION
		,MARKET_AREA_U MARKET
		,STATE STATE_CD
		,AM_U ACCT_MGR 
		,AE_ASSIGNED_U AREA_MGR
		,PORTFOLIO_NAME_U PORTFOLIO
		,TRIM(STATE)||TRIM(PROPERTY_ID_U) TRAC_ID
		,EXTRACT (MONTH FROM POSTDATE_U) MN
		,EXTRACT (YEAR FROM POSTDATE_U) YR
		,CASE 
		 WHEN INDEX(PRODUCT_NM_U,'VOICE') GT 0 THEN 'VOIP' 
		 WHEN INDEX(PRODUCT_NM_U,'HSIA') GT 0 THEN 'HSIA' 
		 ELSE 'IPTV' 
		 END PRODUCT_NM
		,CASE PRODUCT_ACTION_CD_U
		 WHEN 'O' THEN 'OUT'
		 WHEN 'I' THEN 'IN'  
		 END DIRECTION
		,PROD_QTY_U QTY
		,NATIONAL_IND_U NATIONAL_ID
		,DWELLING_TYPE_U
		,PROPERTY_NAME_U
		,POSTDATE_U START_DT			
		FROM		
		( -- X
			SELECT 
			STATE
			,PROPERTY_ID_U
			,NATIONAL_IND_U
			,DWELLING_TYPE_U
			,REGION_DESC_U
			,PROPERTY_NAME_U
			,AM_U
			,AE_ASSIGNED_U
			,PORTFOLIO_NAME_U
			,MARKET_AREA_U
			,POSTDATE_U
			,PRODUCT_ID_U
			,PRODUCT_NM_U       
			,PRODUCT_ACTION_CD_U  
			,PROD_QTY_U
			 FROM
			(  -- TOT
				SELECT 
				 STATE     /*   ADDED 9/9/09 PER MIST REQUEST  126821   */
				,BASE.PROPERTY_ID AS PROPERTY_ID_U
				,NATIONAL_IND_U
				,DWELLING_TYPE_U
				,REGION AS REGION_DESC_U
				,PROPERTY_NAME  AS PROPERTY_NAME_U
				,AM  AS AM_U
				,AE_ASSIGNED  AS AE_ASSIGNED_U
				,PORTFOLIO_NAME  AS PORTFOLIO_NAME_U
				,MARKET_AREA  AS MARKET_AREA_U
				,POSTDATE  AS POSTDATE_U
				,BASE.PRODUCT_ID   AS PRODUCT_ID_U
				,PRODUCT_NM           AS PRODUCT_NM_U       
				,PRODUCT_ACTION_CD    AS PRODUCT_ACTION_CD_U  
				,PROD_QTY AS PROD_QTY_U
				FROM
				( -- LEGS
					SELECT  
					 PROPERTY_ID 
					,REGION
					,PROPERTY_NAME
					,NATIONAL_IND  AS  NATIONAL_IND_U
					,DWELLING_TYPE  AS DWELLING_TYPE_U
					,AM
					,AE_ASSIGNED
					,PORTFOLIO_NAME
					,MARKET_AREA
					,POSTDATE
					,PRODUCT_ID   
					,PRODUCT_ACTION_CD             
					,PROD_QTY  
					,STATE   
					FROM
					( -- CHK
						SELECT 
						 S.PROPERTY_ID 
						,SC.REGION
						,SC.PROPERTY_NAME
						,SC.NATIONAL_IND
						,SC.DWELLING_TYPE
						,SC.AM
						,SC.AE_ASSIGNED
						,SC.PORTFOLIO_NAME
						,SC.MARKET_AREA
						,E.EVENT_ID
						,E.POSTDATE
						,E.STATE
						FROM 
						SMARTMOVES.TXSM012_SMOVES_ADDRESSES  
						S
						INNER JOIN
						SMARTMOVES.TXSM101_SO_SCORE 	
						SC
					    ON S.PROPERTY_ID = SC.PROPERTY_ID
						INNER JOIN
						(-- E		  		
				  			SELECT 
							 EVENT_ID
							,POSTDATE
							,HASHROW(ADDRESS) HADDR
							,STATE
							,ZIP_CD					
							FROM
							( -- ADDR
								SELECT	
								 EVENT_ID
								,POSTDATE
								--,SECONDARY_ADDRESS_TXT AS  UNIT
								,TRIM(C.SECONDARY_ADDRESS_TXT) (NAMED UNIT_ADD)     
						         ,CASE WHEN C.STREET_DIRECTION_PREFIX_TXT > ' '      
						             THEN TRIM(C.STREET_DIRECTION_PREFIX_TXT) ||' '||
						                  TRIM(C.STREET_NM)                          
						             ELSE C.STREET_NM                                
						             END (NAMED ADR1)                                
						         ,CASE WHEN C.ADDRESS_HOUSE_NBR > ' '                
						             THEN TRIM(C.ADDRESS_HOUSE_NBR) ||' '|| TRIM(ADR1)    
						             ELSE ADR1                                            
						             END (NAMED ADR2)                                     
						         ,CASE WHEN C.ADDRESS_SUFFIX_TYPE_CD > ' '                
						             THEN TRIM(ADR2) ||' '|| TRIM(C.ADDRESS_SUFFIX_TYPE_CD)
						             ELSE ADR2                                            
						             END (NAMED ADR3)                                     
						         ,CASE WHEN C.STREET_DIRECTION_SUFFIX_TXT > ' '           
						             THEN TRIM(ADR3) ||' '||                              
						                  TRIM(C.STREET_DIRECTION_SUFFIX_TXT)             
						             ELSE ADR3                                            
						             END (NAMED ADDRESS)      
								,C.ZIP_CD
								,C.TERRITORY_CD AS STATE
							    FROM  
							 	ENTERPRISE_RETAIL_VIEWS.VCCG559_ACCOUNT_CNTCT_GEO_ADDR 
								C  
								INNER JOIN 
								( --H
									SELECT 
		  							 H205.REPOSITORY_AFFILIATE_ACCT_ID  RAAID  
							 		,H205.EVENT_ID
									,H205.SERVICE_ORDER_NBR        		SONBR
									,H205.EVENT_POSTED_DT    			POSTDATE
									--,CASE WHEN O.SALES_CHANNEL_CD   = 'WEB' THEN 'ONLINE' ELSE O.SALES_CHANNEL_CD END AS SALES_CHANNEL_CD            
									FROM
									 ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT 
									 H205			 
	---------------------------------------------------------------------------------------------------------------------------------------------------------------												
									WHERE
									--use this line for a full load   
									--H205.EVENT_POSTED_DT > DATE-385
									--only get current month's sales 
									EXTRACT(MONTH from EVENT_POSTED_DT) = EXTRACT(MONTH from (DATE-1))
									AND EXTRACT(YEAR from EVENT_POSTED_DT) = EXTRACT(YEAR from (DATE-1))
									AND  H205.DATA_SOURCE_CD IN (150)	
									AND RAAID > 0
								) 
								H
								ON  C.REPOSITORY_AFFILIATE_ACCT_ID = H.RAAID
								AND C.ZIP_CD <> ' '
								AND C.ADDRESS_USAGE_CD = 'S' 
							) 
							ADDR
						) 
						E
						ON  S.ZIP = E.ZIP_CD
						AND HASHROW(TRIM(S.ADDRESS)) = HADDR
						AND (SC.PR_STATUS = 'ACTIVE' AND C_STATUS = 'ACTIVE' ) 
						UNION
						SELECT 
						 PROPERTY_ID 
						,REGION
						,PROPERTY_NAME
						,NATIONAL_IND
						,DWELLING_TYPE
						,AM
						,AE_ASSIGNED
						,PORTFOLIO_NAME
						,MARKET_AREA
						,EVENT_ID
						,POSTDATE
						,STATE
						FROM 
						(
						SELECT 
						 S.PROPERTY_ID 
						,TRIM(S.SERVICE_BUILDING_NBR) || ' ' || TRIM(S.ADDRESS)  AS  STREET_ADDRESS
						,SC.REGION
						,SC.PROPERTY_NAME
						,SC.NATIONAL_IND
						,SC.DWELLING_TYPE
						,SC.AM
						,SC.AE_ASSIGNED
						,SC.PORTFOLIO_NAME
						,SC.PR_STATUS
						,SC.C_STATUS
						,SC.MARKET_AREA
						,' ' AS DISTRIBUTION_AREA
						,E.RAAID
						,E.EVENT_ID
						,E.SONBR
						,DISC_RSN_CD_U
						,DISC_RSN_U   
						,SALES_CODE
						,SALES_CHANNEL_CD
						,BAN
						,ORDER_TYPE
						,SOURCE_CD
						,E.POSTDATE
						,E.CONTACT_NM
						,E.CITY_NM
						,E.STATE
						,E.ZIP_CD
						FROM SMARTMOVES.TXSM012_SMOVES_ADDRESSES  
						S
						,SMARTMOVES.TXSM101_SO_SCORE  	
						SC
						,(
								SELECT RAAID
								,EVENT_ID
								,SONBR
								,DISC_RSN_CD_U
								,DISC_RSN_U   
								,SALES_CODE
								,SALES_CHANNEL_CD
								,BAN
								,ORDER_TYPE
								,SOURCE_CD
								,POSTDATE
								,CONTACT_NM
								,CITY_NM
								,ADDRESS
								,STATE
								,ZIP_CD	
								FROM
								(
								SELECT
								 REPOSITORY_AFFILIATE_ACCT_ID RAAID
								,EVENT_ID
								,SONBR
								,DISC_RSN_CD_U
								,DISC_RSN_U   
								,SALES_CODE
								,SALES_CHANNEL_CD
								,BAN
								,ORDER_TYPE
								,SOURCE_CD
								,POSTDATE
								,ADDRESS_ID
								,CONTACT_NM
								,CITY_NM
								,PRIMARY_ADDRESS_TXT
								,TRIM(C.SECONDARY_ADDRESS_TXT) (NAMED UNIT_ADD)     
								,CASE WHEN C.STREET_DIRECTION_PREFIX_TXT > ' '      
								THEN TRIM(C.STREET_DIRECTION_PREFIX_TXT) ||' '||
								TRIM(C.STREET_NM)                          
								ELSE C.STREET_NM                                
								END (NAMED ADR1)                                
								,CASE WHEN C.ADDRESS_HOUSE_NBR > ' '                
								THEN TRIM(C.ADDRESS_HOUSE_NBR) ||' '|| TRIM(ADR1)    
								ELSE ADR1                                            
								END (NAMED ADR2)                                     
								,CASE WHEN C.ADDRESS_SUFFIX_TYPE_CD > ' '                
								THEN TRIM(ADR2) ||' '|| TRIM(C.ADDRESS_SUFFIX_TYPE_CD)
								ELSE ADR2                                            
								END (NAMED ADR3)                                     
								,CASE WHEN C.STREET_DIRECTION_SUFFIX_TXT > ' '           
								THEN TRIM(ADR3) ||' '||                              
								TRIM(C.STREET_DIRECTION_SUFFIX_TXT)             
								ELSE ADR3                                            
								END (NAMED ADDRESS)      
								,C.ZIP_CD
								,C.TERRITORY_CD AS STATE
								FROM  
								ENTERPRISE_RETAIL_VIEWS.VCCG559_ACCOUNT_CNTCT_GEO_ADDR 
								C
								,( -- S
										SELECT 
										 H205.REPOSITORY_AFFILIATE_ACCT_ID  RAAID  
										,H205.EVENT_ID
										,H205.SERVICE_ORDER_NBR        		SONBR
										,DISC_RSN_CD_U
										,DISC_RSN_U     
										,O.BAN
										,H205.EVENT_POSTED_DT    			POSTDATE
										,H205.EVENT_COMPLETION_DT    		CMPLDATE 
										,H205.EVENT_CLASS_CD				ORDER_TYPE
										,H205.DATA_SOURCE_CD				SOURCE_CD
										,SUBSTR(H205.ORIGINATING_SALES_CD,1,7) AS SALES_CODE     
										,CASE WHEN O.SALES_CHANNEL_CD   = 'WEB' THEN 'ONLINE' ELSE O.SALES_CHANNEL_CD END AS SALES_CHANNEL_CD            
										FROM
										ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT H205
										,( --0
												SELECT 
												 O.ORDER_ID	
												,O.SERVICE_ORDER_CD
												,O.BAN
												,SALES_CHANNEL_CD
												,DISC_RSN_CD_U     
												,DISC_RSN_U
												FROM	  TELCO_UNREG_RETAIL_VIEWS.VCTV800_ORDER	
												O					
												LEFT OUTER JOIN 
												(
														SELECT 
														 A.ORDER_ID
														,A.ORDER_ACTION_TYPE_CD
														,A.ORDER_ACTION_REASON_CD   DISC_RSN_CD_U
														,C.ORDER_ACTION_REASON_DESC DISC_RSN_U
														FROM
														TELCO_UNREG_RETAIL_VIEWS.VCTV801_ORDER_ACTION  	 
														A 
														,TELCO_UNREG_RETAIL_VIEWS.VCTV804_ORDER_ACTION_RSN_DESC  
														C
														WHERE  A.ORDER_ACTION_REASON_CD = C.ORDER_ACTION_REASON_CD
														AND	A.ORDER_ACTION_TYPE_CD = C.ORDER_ACTION_TYPE_CD
														AND A.ORDER_ACTION_REVISION_TYPE_CD = C.ORDER_ACTION_REVISION_TYPE_CD
														AND	A.CURRENT_ORDER_ACTION_STATUS_CD = 'DO'
														AND A.ORDER_ACTION_TYPE_CD IN ('CH', 'CE') 
												) 
												D
												ON O.ORDER_ID = D.ORDER_ID
										) 	
										O
										WHERE   
										--use this line for a full load   
										--H205.EVENT_POSTED_DT > DATE-385
										--only get current month's sales 
										--------------------------------------------------------------------------------
										EXTRACT(MONTH from EVENT_POSTED_DT) = EXTRACT(MONTH from (DATE-1))
										AND EXTRACT(YEAR from EVENT_POSTED_DT) = EXTRACT(YEAR from (DATE-1))										AND H205.DATA_SOURCE_CD IN (150)
										AND O.ORDER_ID = SONBR
										AND RAAID > 0
										GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12    
								) 
								S
								WHERE C.REPOSITORY_AFFILIATE_ACCT_ID = S.RAAID
								AND C.ZIP_CD <> ' '
								AND C.ADDRESS_USAGE_CD = 'S' ) BASE
								GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16 
						) 
						E
						WHERE TRIM(STREET_ADDRESS) = TRIM(E.ADDRESS)
						AND S.SERVICE_CITY_NM = E.CITY_NM
						AND S.SERVICE_CITY_NM <> ' '
						AND E.STATE = 'CT' 
						AND S.PROPERTY_ID = SC.PROPERTY_ID
						AND (SC.PR_STATUS = 'ACTIVE' AND C_STATUS = 'ACTIVE' )
						) CT_ADDR					) 
					CHK
					LEFT OUTER JOIN 
					( --ACT
						SELECT 
						 D.EVENT_ID
						,D.PRODUCT_ID                    
						,CASE WHEN INWARD_OUTWARD_CD = 1 THEN 'I' ELSE 'O' END AS PRODUCT_ACTION_CD             
						,SUM(D.PRODUCT_QTY)    PROD_QTY                              
						FROM 
						ENTERPRISE_RETAIL_VIEWS.VCCH207_SVC_ORD_PROD_EVENT  
						D
						WHERE  D.DATA_SOURCE_CD IN (150)
						AND D.INWARD_OUTWARD_CD  IN (1,4)
						AND D.PRODUCT_ID IN
						(
							10005132	,--U-VERSE VIDEO - BASIC PACKAGE  - U450
							10005022	,--U-VERSE VIDEO - BASIC PACKAGE - 100 CHNLS
							10005020	,--U-VERSE VIDEO - BASIC PACKAGE - 200 CHNLS
							10005021	,--U-VERSE VIDEO - BASIC PACKAGE - 300 CHNLS
							10005023	,--U-VERSE VIDEO - BASIC PACKAGE - 400 CHNLS
							10005134	,--U-VERSE VIDEO - BASIC PACKAGE - BH200
							10005139	,--U-VERSE VIDEO - BASIC PACKAGE - BP200
							10005024	,--U-VERSE VIDEO - BASIC PACKAGE - U - FAMILY
							10005137	,--U-VERSE VIDEO - BASIC PACKAGE - U200 LATINO
							10005140	,--U-VERSE VIDEO - BASIC PACKAGE - UBASIC
							--10005138	,--U-VERSE VIDEO - BASIC PACKAGE - UCOMP
							--10005136	,--U-VERSE VIDEO - BASIC PACKAGE - UDEMO
							10005133	,--U-VERSE VIDEO - BASIC PACKAGE - UGOV
							10005135	,--U-VERSE VIDEO - BASIC PACKAGE - UU300
							10005150	,--U-VERSE HSIA - BASIC
							10005148	,--U-VERSE HSIA - BASIC -STATIC
							10005006	,--U-VERSE HSIA - ELITE-DYNAMIC
							10005009	,--U-VERSE HSIA - ELITE-STATIC
							10005112	,--U-VERSE HSIA - MAX -DYNAMIC
							10005113	,--U-VERSE HSIA - MAX -STATIC
							10005151	,--U-VERSE HSIA - MAX TURBO
							10005149	,--U-VERSE HSIA - MAX TURBO -STATIC
							10005116	,--U-VERSE HSIA - MAX18 -DYNAMIC
							10005117	,--U-VERSE HSIA - MAX18 -STATIC
							10005005	,--U-VERSE HSIA - PRO-DYNAMIC
							10005008	,--U-VERSE HSIA - PRO-STATIC
							10005004	,--U-VERSE HSIA-EXPRESS-DYNAMIC
							10005007	,--U-VERSE HSIA-EXPRESS-STATIC
							10005108	,--AT&T U-VERSE VOICE 1000
							10005157	,--AT&T U-VERSE VOICE 250
							10005107	,--AT&T U-VERSE VOICE 500
							10005109	,--AT&T U-VERSE VOICE MOVEMENT
							10005106	--AT&T U-VERSE VOICE UNLIMITED
						 )
						GROUP BY 1,2,3
					) 
					ACT
					ON CHK.EVENT_ID = ACT.EVENT_ID
				)
				BASE
				LEFT OUTER JOIN	
				( --P
					SELECT 
					 PRODUCT_ID
					,PRODUCT_NM
					FROM
					ENTERPRISE_RETAIL_VIEWS.VCCR200_PRODUCT   
					WHERE PRODUCT_LAST_EFFECTIVE_DT > DATE
					AND PRODUCT_ID > 0
				)  
				P
				ON BASE.PRODUCT_ID = P.PRODUCT_ID
			) 
			TOT					
	
			) 
			X	--ACC_BASE_DATA		
	)
	C
